<%@Page Language="VB"%>
<%@Import Namespace="System.Data" %>
<%@Import Namespace="System.Data.SqlClient" %>
<%@Import Namespace="System.Xml" %>
<%@ Register TagPrefix="Khang" TagName="connect" Src="connect-strings.ascx" %>
<html><head>
<title>Reading XML Direct From 
SQL Server With An XmlReader</title>
</head>
<body bgcolor="#ffffff">
<span class="heading">Reading XML Direct 
From SQL Server With An XmlReader
</span><hr />
<Khang:connect id="ctlConnectStrings" 
runat="server"/>
</span></b></div><p />
<div id="outError" runat="server" />
<div id="outResult" runat="server" />

<script language="vb" runat="server">
Sub Page_Load()
   Dim strConnect As String
   strConnect = ctlConnectStrings.SQLConnectionString
   Dim strSelect As String
   strSelect = "SELECT * FROM tblSubCategories "
   strSelect += " FOR XML AUTO"  
   'create a new StringBuilder to hold the results
   Dim objStrBuilder As New StringBuilder()
   Try
      Dim objConnect As SqlConnection=New SqlConnection(strConnect)
      'create a new Command using the connection object and select statement
      Dim objCommand As New SqlCommand(strSelect, objConnect)
      'declare a variable to hold an XmlTextReader object
      Dim objXTReader As XmlTextReader
      'open the connection to the database
      objConnect.Open()
      'execute the SQL statement against the command to fill the XmlReader
      objXTReader = objCommand.ExecuteXmlReader()
      'read the first result to initialize the reader
      objXTReader.Read()
      'and then read remainder into the StringBuilder as well
      objStrBuilder.Append(objXTReader.GetRemainder().ReadToEnd())
      'close the XmlReader and Connection
      objXTReader.Close()
      objConnect.Close()
   Catch objError As Exception
	outError.InnerHtml = _
	"<b>* Error while accessing " & _
	" data</b>.<br />" _
          	& objError.Message & "<br />" & _
	objError.Source
      Exit Sub  
   End Try
   'display the results as Text to show XML elements
   outError.InnerText = objStrBuilder.ToString()
End Sub
</script>
</body>
</html>
